package cn.edu.sdjzu.xg2121.dao;

import cn.edu.sdjzu.xg2121.domain.Teacher;
import cn.edu.sdjzu.xg2121.util.JdbcHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TeacherDao {
    private static TeacherDao teacherDao = new TeacherDao();
    public TeacherDao(){}
    public static TeacherDao getInstance(){
        return teacherDao;
    }
    public Teacher find(int department_id, Connection connection) throws SQLException {
        Teacher teacher = null;
        PreparedStatement pstmt = connection.prepareStatement("select * from teacher where department_id = ?");
        pstmt.setInt(1,department_id);
        ResultSet resultSet = pstmt.executeQuery();
        if (resultSet.next()){
            String name = resultSet.getString("name");
            teacher = new Teacher(name);
        }
        return teacher;
    }
    public Teacher add(Teacher teacher) throws SQLException {
        Connection connection = JdbcHelper.getConn();
        String addTeacher_sql = "INSERT INTO teacher ( name, department_id)VALUES"+"(?,?)";
        PreparedStatement stmt_add_all = connection.prepareStatement(addTeacher_sql);
        stmt_add_all.setString(1,teacher.getName());
        stmt_add_all.setInt(2,teacher.getDepartment().getId());
        stmt_add_all.executeUpdate();
        JdbcHelper.close(stmt_add_all,connection);
        return null;
    }
    public Teacher delete(Teacher teacher) throws SQLException {
        Connection connection = JdbcHelper.getConn();
        String foreign_key1 = "set foreign_key_checks = 0";
        PreparedStatement stmt_update_name1 = connection.prepareStatement(foreign_key1);

        String updateTeacher_sql1 = "delete from teacher where id = ?";
        PreparedStatement stmt_update_name = connection.prepareStatement(updateTeacher_sql1);
        stmt_update_name.setInt(1,teacher.getId());

        String foreign_key2 = "set foreign_key_checks = 1";
        PreparedStatement stmt_update_name2 = connection.prepareStatement(foreign_key2);

        stmt_update_name.executeUpdate();
        JdbcHelper.close(stmt_update_name,connection);
        System.out.println(teacher.toString());
        return null;
    }
    public Teacher update(Teacher teacher) throws SQLException {
        Connection connection = JdbcHelper.getConn();
        String updateTeacher_sql1 = "update teacher set name = ? , department_id = ? where id = ?";
        PreparedStatement stmt_update_name = connection.prepareStatement(updateTeacher_sql1);
        stmt_update_name.setString(1,teacher.getName());
        stmt_update_name.setInt(2,teacher.getDepartment().getId());
        stmt_update_name.setInt(3,teacher.getId());
        stmt_update_name.executeUpdate();
        JdbcHelper.close(stmt_update_name,connection);
        return null;
    }
}
